import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine

# 设置中文字体和图片清晰度
plt.rcParams["font.family"] = ["SimHei", "Heiti TC"]
# plt.rcParams['figure.dpi'] = 300

# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 
postgres_schema = "datawarehouse" 

# 建立数据库连接
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
engine = create_engine(conn_string)
conn = engine.connect()

# 从数据库查询数据的函数
def query_data_from_database():
    """从数据库查询三个表的数据"""
    # 客户表查询
    query_customer = f"""
    SELECT * FROM {postgres_schema}.dim_customer;
    """
    dim_customer = pd.read_sql(query_customer, conn)
    
    # 产品表查询
    query_product = f"""
    SELECT * FROM {postgres_schema}.dim_product;
    """
    dim_product = pd.read_sql(query_product, conn)
    
    # 销售表查询
    query_sales = f"""
    SELECT * FROM {postgres_schema}.fact_sales;
    """
    fact_sales = pd.read_sql(query_sales, conn)
    
    return dim_customer, dim_product, fact_sales

def plot_gender_product_preference(dim_customer, fact_sales, dim_product):
    # 1. 合并表：客户 + 销售 + 产品
    merged = pd.merge(dim_customer, fact_sales, on='cust_num', how='left')
    merged = pd.merge(merged, dim_product, on='prd_key', how='left')
    
    # 2. 按性别 + 产品类别分组，统计销售数量
    gender_category = merged.groupby(['cust_gender', 'prd_category'])['sls_quantity'].sum().reset_index()
    
    # 3. 画图：分组柱状图
    plt.figure(figsize=(12, 6))
    sns.barplot(
        x='prd_category', 
        y='sls_quantity', 
        hue='cust_gender', 
        data=gender_category
    )
    plt.title('不同性别客户的产品类别偏好', fontsize=14)
    plt.xlabel('产品类别', fontsize=12)
    plt.ylabel('总购买数量', fontsize=12)
    plt.tight_layout()
    plt.show()

def plot_country_sales_trend(dim_customer, fact_sales, dim_product):
    # 1. 合并表 + 计算销售额
    merged = pd.merge(dim_customer, fact_sales, on='cust_num', how='left')
    merged = pd.merge(merged, dim_product, on='prd_key', how='left')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']
    
    # 2. 日期处理：转成月份
    merged['order_month'] = pd.to_datetime(merged['sls_order_date']).dt.to_period('M')
    
    # 3. 按国家 + 月份分组，算总销售额
    country_revenue = merged.groupby(['cust_country', 'order_month'])['revenue'].sum().reset_index()
    country_revenue['order_month'] = country_revenue['order_month'].astype(str)  # 转成字符串方便画图
    
    # 4. 画图：多国家趋势对比
    plt.figure()
    sns.lineplot(
        x='order_month', 
        y='revenue', 
        hue='cust_country', 
        data=country_revenue,
        marker='o'
    )
    plt.title('不同国家客户的销售额月度趋势')
    plt.xlabel('月份')
    plt.ylabel('总销售额')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.show()

def plot_customer_value_segment(dim_customer, fact_sales, dim_product):
    # 1. 合并表 + 计算每个客户的总消费金额
    merged = pd.merge(dim_customer, fact_sales, on='cust_num', how='left')
    merged = pd.merge(merged, dim_product, on='prd_key', how='left')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']
    
    # 2. 按客户聚合，算总消费
    customer_total = merged.groupby('cust_num')['revenue'].sum().reset_index()
    customer_total.columns = ['cust_num', 'total_revenue']
    
    # 3. 分层
    customer_total['value_level'] = pd.qcut(
        customer_total['total_revenue'], 
        q=[0, 0.3, 0.7, 1], 
        labels=['低价值', '中价值', '高价值']
    )
    
    # 4. 画图
    plt.figure(figsize=(12, 5))
    plt.subplot(1, 2, 1)
    customer_total['value_level'].value_counts().plot.pie(autopct='%1.1f%%')
    plt.title('客户价值分层占比', fontsize=14)
    
    plt.subplot(1, 2, 2)
    # 关联国家，看高价值客户分布
    high_value_countries = pd.merge(
        customer_total[customer_total['value_level'] == '高价值'],
        dim_customer[['cust_num', 'cust_country']],
        on='cust_num'
    )['cust_country'].value_counts()
    high_value_countries.plot.bar()
    plt.title('高价值客户的国家分布', fontsize=14)
    
    plt.tight_layout()
    plt.show()


def plot_new_old_customer(dim_customer, fact_sales, dim_product):
    # 1. 定义新老客户
    cutoff_date = pd.Timestamp('2011-01-01')
    dim_customer['customer_type'] = dim_customer['cust_create_date'].apply(
        lambda x: '新客户' if pd.to_datetime(x) >= cutoff_date else '老客户'
    )
    
    # 2. 合并表 + 计算销售额
    merged = pd.merge(dim_customer, fact_sales, on='cust_num', how='left')
    merged = pd.merge(merged, dim_product, on='prd_key', how='left')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']
    
    # 3. 按新老客户 + 产品类别分组
    new_old_category = merged.groupby(['customer_type', 'prd_category'])['revenue'].sum().reset_index()
    
    # 4. 画图：对比新老客户的产品偏好
    plt.figure(figsize=(12, 6))
    sns.barplot(
        x='prd_category', 
        y='revenue', 
        hue='customer_type', 
        data=new_old_category
    )
    plt.title('新老客户的产品类别贡献对比', fontsize=14)
    plt.xlabel('产品类别', fontsize=12)
    plt.ylabel('总销售额', fontsize=12)
    plt.tight_layout()
    plt.show()

def plot_cross_segment_heatmap(dim_customer, fact_sales, dim_product):
    # 1. 合并表 + 计算销售额
    merged = pd.merge(dim_customer, fact_sales, on='cust_num', how='left')
    merged = pd.merge(merged, dim_product, on='prd_key', how='left')
    merged['revenue'] = merged['sls_quantity'] * merged['sls_price']
    
    # 2. 按 性别 + 国家 + 产品类别 分组，算总销售额
    cross_group = merged.groupby(['cust_gender', 'cust_country', 'prd_category'])['revenue'].sum().reset_index()
    
    # 3. 整理成热力图需要的矩阵（以 性别+国家 为行，产品类别为列）
    heatmap_data = cross_group.pivot_table(
        index=['cust_gender', 'cust_country'], 
        columns='prd_category', 
        values='revenue', 
        aggfunc='sum',
        fill_value=0
    )
    
    # 4. 画图
    plt.figure(figsize=(12, 8))
    sns.heatmap(heatmap_data, annot=True, fmt='.0f', cmap='Blues')
    plt.title('多维度交叉：性别×国家×产品类别的销售额热力图', fontsize=14)
    plt.xlabel('产品类别', fontsize=12)
    plt.ylabel('性别×国家', fontsize=12)
    plt.tight_layout()
    plt.show()

def main():
    try:
        # 1. 查数据
        dim_customer, dim_product, fact_sales = query_data_from_database()
        
        # 3. 新增切片分析
        plot_gender_product_preference(dim_customer, fact_sales, dim_product)
        plot_country_sales_trend(dim_customer, fact_sales, dim_product)
        plot_customer_value_segment(dim_customer, fact_sales, dim_product)
        plot_new_old_customer(dim_customer, fact_sales, dim_product)
        plot_cross_segment_heatmap(dim_customer, fact_sales, dim_product)
        
        print("可视化完成")
        
    except Exception as e:
        print(f"执行主程序时出错: {str(e)}")
    finally:
        # 关连接
        if conn:
            conn.close()
        if engine:
            engine.dispose()
        print("已关闭数据库连接")


# 修改主程序入口
if __name__ == "__main__":
    main()